using System.Data.SQLite;
using System.Text.RegularExpressions;
using Dapper;

namespace Neighbors.Database;

public class FlatRepository : IFlatRepository
{
    private readonly SQLiteConnection _connection = new(Secrets.GetConnectionString());
    private readonly string _sqlSelectQuery =
        $@"SELECT number_flat as {nameof(Flat.NumberFlat)},
                                                  number_floors as {nameof(Flat.NumberFloors)},
                                                  name_lodger as {nameof(Flat.NameLodger)},
                                                  number_phone as {nameof(Flat.PhoneNumber)}
                                                  FROM neighbors";
    private readonly string _sqlUpdateQuery = "UPDATE neighbors SET";

    public async Task<Flat> GetNeighborsByChatIdAsync(long chatId)
    {
        var selectQuery = $"{_sqlSelectQuery} WHERE id_telegram = @Id";
        IEnumerable<Flat> result = await _connection.QueryAsync<Flat>(
            selectQuery,
            new { Id = chatId }
        );
        return result.FirstOrDefault()!;
    }

    public async Task<Flat> GetNeighborsByNumberFlatAsync(int numberFlat)
    {
        var selectQuery = $"{_sqlSelectQuery} WHERE number_flat = @NumberFlat";
        IEnumerable<Flat> result = await _connection.QueryAsync<Flat>(
            selectQuery,
            new { NumberFlat = numberFlat }
        );
        return result.FirstOrDefault();
    }

    public async Task<string> UpdateNameNeighborsByChatIdAsync(long chatId, string nameLodger)
    {
        var updateQuery = $"{_sqlUpdateQuery} name_lodger = @NameLodger WHERE id_telegram = @Id";
        await _connection.ExecuteAsync(updateQuery, new { NameLodger = nameLodger, Id = chatId });
        var selectQuery = $"{_sqlSelectQuery} WHERE id_telegram = @Id";
        IEnumerable<Flat> result = await _connection.QueryAsync<Flat>(
            selectQuery,
            new { Id = chatId }
        );

        if (result.FirstOrDefault()!.NameLodger == nameLodger)
            return "Данные изменены!";

        return "Данные не удалось изменить!";
    }

    public async Task<string> UpdatePhoneNeighborsByChatIdAsync(long chatId, string phoneLodger)
    {
        string pattern = @"^79\d{9}$";
        Regex regex = new Regex(pattern);

        if (!regex.IsMatch(phoneLodger))
            return "Неверный формат номера!";

        var updateQuery = $"{_sqlUpdateQuery} number_phone = @PhoneLodger WHERE id_telegram = @Id";
        await _connection.ExecuteAsync(updateQuery, new { PhoneLodger = phoneLodger, Id = chatId });
        var selectQuery = $"{_sqlSelectQuery} WHERE id_telegram = @Id";
        IEnumerable<Flat> result = await _connection.QueryAsync<Flat>(
            selectQuery,
            new { Id = chatId }
        );

        if (result.FirstOrDefault().PhoneNumber == long.Parse(phoneLodger))
            return "Данные изменены!";

        return "Данные не удалось изменить!";
    }
}
